#include "stusql.h"
#include <QMessageBox>
#include <QtSql/QSql>
#include <QCoreApplication>
#include <QSqlDatabase>
#include <QDebug>
#include <QSqlQuery>
#include <synchapi.h>

stuSql* stuSql::ptrstuSql = nullptr;

stuSql::stuSql(QObject *parent) : QObject(parent)
{
    init();

    StuInfo s;

//    s.name = "xs";
//    for(int i = 0; i < 10; i++){
//        addStu(s);
//    }

//    getStuCnt();
//    getPageStu(2, 3);
      //delStu(18);

    UserInfo info;
    info.username = "xxx";
    info.password = "123456";
    info.aut = "admin";

    AddUser(info);
    AddUser(info);

    auto l = getAllUser();
    qDebug() << isExit("xxx");
    info.password = "666";
    updateuser(info);

    // Sleep(5000);
    delUser("xxx");

}

void stuSql::init()
{
    //打印Qt支持的数据库驱动
    // qDebug()<<QSqlDatabase::drivers();

    if(QSqlDatabase::drivers().isEmpty()){
        qDebug() << "No database drivers found";
    };
    m_db = QSqlDatabase::addDatabase("QSQLITE");
#if 0
    // 获取当前应用程序输出目录
    auto str = QCoreApplication::applicationDirPath();
    qDebug() << str;
#endif
    m_db.setDatabaseName("D:/C++/qt-learning/Project/data.db");
    if(!m_db.open()){
        qDebug() << "db not open";
    }
}

quint32 stuSql::getStuCnt()
{
    QSqlQuery sql(m_db);
    sql.exec("select count(id) from student");
    quint32 uiCnt = 0;
    while(sql.next())
    {
        // qDebug() << sql.value(0).toUInt();
        uiCnt = sql.value(0).toUInt();
    }
    return uiCnt;
}

QList<StuInfo> stuSql::getPageStu(quint32 page, quint32 uiCnt)
{
    QList<StuInfo> l;
    QSqlQuery sql(m_db);
    QString strSql = QString("select * from student order by id limit %1 offset %2").arg(uiCnt).arg(page * uiCnt);
    sql.exec(strSql);

    StuInfo info;
    while(sql.next())
    {
        info.id = sql.value(0).toUInt();
        info.name = sql.value(1).toString();
        info.age =  sql.value(2).toUInt();
        info.grade = sql.value(3).toUInt();
        info.uiclass = sql.value(4).toUInt();
        info.studentid = sql.value(5).toUInt();
        info.phone = sql.value(6).toString();
        info.wechat = sql.value(7).toString();
        l.push_back(info);
    }
    return l;
}

bool stuSql::addStu(StuInfo info)
{
    QSqlQuery sql(m_db);
    QString strSql = QString("insert into student values(null, '%1', %2, %3, %4, %5, '%6', '%7')").
            arg(info.name).
            arg(info.age).
            arg(info.grade).
            arg(info.uiclass).
            arg(info.studentid).
            arg(info.phone).
            arg(info.wechat);
    return sql.exec(strSql);
}

bool stuSql::delStu(int id)
{
    QSqlQuery sql(m_db);
    return sql.exec(QString("delete from student where id = %1").arg(id));
}

bool stuSql::cleaStuTable()
{
    QSqlQuery sql(m_db);
    // 清除所有数据并且包括 id 自增
    sql.exec("delete from student");
    return sql.exec("delete from sqlite_sequence where name = 'student'");
}

bool stuSql::UpdateStuInfo(StuInfo info)
{
    QSqlQuery sql(m_db);
    QString strSql = QString("update student set name='%1', age=%2, grade=%3, class=%4, studentid=%5, "
                             "phone='%6', wechat='%7' where id=%8").
            arg(info.name).
            arg(info.age).
            arg(info.grade).
            arg(info.uiclass).
            arg(info.studentid).
            arg(info.phone).
            arg(info.wechat).arg(info.id);

    return sql.exec(strSql);
}

QList<UserInfo> stuSql::getAllUser()
{
    QList<UserInfo> l;
    QSqlQuery sql(m_db);
    QString strSql = QString("select * from username");
    sql.exec(strSql);

    UserInfo info;
    while(sql.next())
    {
        info.username = sql.value(0).toString();
        info.password = sql.value(1).toString();
        info.aut =  sql.value(2).toString();
        l.push_back(info);
    }

    return l;
}

bool stuSql::isExit(QString strUser)
{
    QSqlQuery sql(m_db);
    QString strSql = QString("select * from username where username='%1'");
    sql.exec(strSql.arg(strUser));
    return sql.next();
}

bool stuSql::updateuser(UserInfo info)
{
    QSqlQuery sql(m_db);
    QString strSql = QString("update username set password='%1',aut='%2' where username='%3'").
            arg(info.password).arg(info.aut).arg(info.username);

    return sql.exec(strSql);
}

bool stuSql::AddUser(UserInfo info)
{
    QSqlQuery sql(m_db);
    QString strSql = QString("insert into username values('%1', '%2', '%3')").
            arg(info.username).
            arg(info.password).
            arg(info.aut);

    return sql.exec(strSql);
}

bool stuSql::delUser(QString strUserName)
{
    QSqlQuery sql(m_db);
    return sql.exec(QString("delete from username where username='%1'").arg(strUserName));
}


















